package com.sxfq.loansupermarket.backsystem.modular.marketing.controller;

import com.baomidou.mybatisplus.plugins.Page;
import com.sxfq.loansupermarket.backsystem.common.constant.factory.PageFactory;
import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.Deptinfos;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.Role;
import com.sxfq.loansupermarket.backsystem.core.log.LogManager;
import com.sxfq.loansupermarket.backsystem.core.log.factory.LogTaskFactory;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.HttpKit;
import com.sxfq.loansupermarket.backsystem.core.util.DateUtil;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.SearchReturnVisitInfo;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.*;
import com.sxfq.loansupermarket.backsystem.modular.marketing.service.ReturnVisitStatisticsService;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.DepartmentInfo;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * @author zx
 * @Date
 */
@Controller
@RequestMapping("/returnVisitStatistics")
public class ReturnVisitStatisticsController extends BaseController {

    @Autowired
    private ReturnVisitStatisticsService service;

    private String PREFIX = "/marketing/reservations/";

    @Autowired
    private SqlMapper sqlMapper;

    /**
     * 跳转到市场回访统计页面
     */
    @RequestMapping("/statistics")
    public String statistics(Model model) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getArealist() == null) {
            String sql1 = "select area_abbreviation as areaAbbreviation,area as area from dept a where length(a.pids)>4 " +
                    " and version='" + ShiroKit.getUser().getCompanyVersion() + "' group by a.area order by a.num desc";
            List<Deptinfos> arealist = sqlMapper.selectList(sql1, Deptinfos.class);
            ShiroKit.getUser().setArealist(arealist);
        }
        if (ShiroKit.getUser().getDepartmentList() == null) {
            String getListSql = "select a.department as department from " + ShiroKit.getUser().getPay_service_grade_info() + " a where a.is_upper=1 group by a.department order by a.id;";
            List<DepartmentInfo> departmentList=sqlMapper.selectList(getListSql,DepartmentInfo.class);
            ShiroKit.getUser().setDepartmentList(departmentList);
        }
        model.addAttribute("outpatientManagementList", ShiroKit.getSessionAttr("outpatientManagementList"));
        model.addAttribute("departmentList",ShiroKit.getUser().getDepartmentList());
        model.addAttribute("arealist", ShiroKit.getUser().getArealist());
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "revisit_return_visit_statistics.html";
    }

    /**
     * 回访统计
     */
    @RequestMapping("/list")
    @ResponseBody
    public Object reservationsRecord(SearchReturnVisitStatistics info, HttpServletRequest request) {
        Page<ReturnVisitStatistics> page = new PageFactory<ReturnVisitStatistics>().defaultPage();
        try {
            info.setStartIndex(page.getSize() * (page.getCurrent() - 1));
            info.setLimitIndex(page.getSize());
            page.setCurrent(0);
            List<ReturnVisitStatistics> list = service.selectPageListByStatusMap(page, info);
            if (list.size() == 0) {
                info.setStartIndex(0);
                list = service.selectPageListByStatusMap(page, info);
            }
            page.setRecords(list);
            page.setTotal(service.getQueryPagesCount(page, info));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return super.packForBT(page);
    }


    /**
     * 统计查询
     */
    @RequestMapping(value = "/tongji", method = RequestMethod.GET)
    @ResponseBody
    public List<ReservationsInfo> change(String param1, String param2, String param3, String param4, String param5, String param6,
                                         String param7, String param8, String param9,String param10, String param11, String param12) throws Exception {
        //1.获取当前日期，格式为yyyy-MM-dd hh:mm:ss
        String currentDate = DateUtil.getTime(new Date()).substring(0, 10);//取今天的年月日
        //获取昨天日期字符串 格式为yyyy-MM-dd hh:mm:ss
        String lastDate = DateUtil.getAfterDayDate("-1").substring(0, 10);//取昨日 的年月日
        //获取当月的字符串
        String currerntMonth = currentDate.substring(0, 7);//yyyy-mm
        //获取上月日期
        String lastMonth = DateUtil.getAfterDayMonth("-1").substring(0, 7);//取上月的年月
        String reservationsInfo = "";
        if (StringUtils.isNotEmpty(param1) && !"请选择".equals(param1)) {
            String outpatientName = "";
            String outpatientName2[] = param1.split(",");
            if (outpatientName2.length == 1) {
                outpatientName = outpatientName2[0];
            } else {
                for (int i = 0; i < outpatientName2.length; i++) {
                    if (i == (outpatientName2.length - 1)) {
                        outpatientName += "'" + outpatientName2[i];
                    } else if (i == 0) {
                        outpatientName += outpatientName2[i] + "'" + ",";
                    } else {
                        outpatientName += "'" + outpatientName2[i] + "'" + ",";
                    }
                }
            }
            reservationsInfo += " and t.outpatient_name in ( '" + outpatientName + "') ";
        }
        if (StringUtil.isNotEmpty(param9) && !"请选择".equals(param9)) {
            String area = "";
            String area2[] = param9.split(",");
            if (area2.length == 1) {
                area = area2[0];
            } else {
                for (int i = 0; i < area2.length; i++) {
                    if (i == (area2.length - 1)) {
                        area += "'" + area2[i];
                    } else if (i == 0) {
                        area += area2[i] + "'" + ",";
                    } else {
                        area += "'" + area2[i] + "'" + ",";
                    }
                }
            }
            reservationsInfo += " and t.outpatient_name in (select a.simplename from dept a where a.area_abbreviation in ('" + area + "')) ";
        }
        if (StringUtils.isNotBlank(param2)) {
            reservationsInfo += " and t1.access_disease  ='" + param2 + "'";
        }
        if (StringUtils.isNotBlank(param3)) {
            reservationsInfo += " and t1.appointment_pay_service='" + param3 + "'";
        }
        if (StringUtils.isNotBlank(param4)) {
            reservationsInfo += " and t.revisit='" + param4 + "'";
        }
        if (StringUtils.isNotBlank(param5)) {
            reservationsInfo += " and t1.filing_time>='" + param5 + "'";
        }
        if (StringUtils.isNotBlank(param6)) {
            reservationsInfo += " and t1.Filing_Time <='" + param6 + "'";
        }
        //姓名   电话   微信  预约号
        if (StringUtils.isNotBlank(param12)) {
            reservationsInfo += " and (t1.customer_name like CONCAT('%"+param12+"%') or t1.customer_phone like CONCAT('%"+param12+"%') " +
                    " or t1.customer_phone2 like CONCAT('%"+param12+"%') " +
                    " or t1.wechat_number like CONCAT('%"+param12+"%') or t1.reservations_num like CONCAT('%"+param12+"%') " +
                    " or t1.identification_number like CONCAT('%"+param12+"%') or t1.key_word like CONCAT('%"+param12+"%') " +
                    " or t1.szm like CONCAT('%"+param12+"%')) ";
        }
        //查询今日回访
        String sql = " select count(*) as countNum from (select t.id as id from reservations_infosc t1 left join return_visit_info t on t.reservations_id = t1.id " +
                "  where left(t.revisit_time,10) = '" + currentDate + "' "+reservationsInfo+"group by t.id  ) b"+
                //查询昨日回访
                " union all select count(*) as countNum from (select t.id as id from reservations_infosc t1 left join return_visit_info t on t.reservations_id = t1.id " +
                "  where left(t.revisit_time,10) = '" + lastDate + "' "+reservationsInfo+" group by t.id  ) b"+
                //查询本周回访
                " union all select count(*) as countNum from (select t.id as id from reservations_infosc t1 left join return_visit_info t on t.reservations_id = t1.id " +
                "  where t.revisit_time >='" + param7 + "' and t.revisit_time <='" + param8 + "' "+reservationsInfo+" group by t.id  ) b"+
                //查询当月回
                " union all select count(*) as countNum from (select t.id as id from reservations_infosc t1  left join return_visit_info t on t.reservations_id = t1.id " +
                "  where left(t.revisit_time,7) = '" + currerntMonth + "' "+reservationsInfo+" group by t.id  ) b"+
                //查询上月回访
                " union all select count(*) as countNum from (select t.id as id from reservations_infosc t1 left join return_visit_info t on t.reservations_id = t1.id " +
                "  where left(t.revisit_time,7) = '" + lastMonth + "' "+reservationsInfo+" group by t.id  ) b";
               /* //时间段查询
                " union all select count(*) as countNum from (select t.id as id from reservations_infosc t1 left join return_visit_info t on t.reservations_id = t1.id " +
                "  where t.revisit_time >='" + param10 + "' and t.revisit_time <='" + param11 + "' "+reservationsInfo+" group by t.id  ) b";*/
        List<ReservationsInfo> infoList = sqlMapper.selectList(sql, ReservationsInfo.class);
        return infoList;
    }

    @RequestMapping(value = "/returnVisitStatisticsExport")
    public void returnVisitStatisticsExport(@Valid SearchReturnVisitStatistics searchInfo, HttpServletResponse response) {
        try {
            List<ReturnVisitStatistics> list = service.returnVisitStatisticsExport(searchInfo);
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("前端导出");
            for (int i = 0; i < 26; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("预约号");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue("姓名");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("手机号码");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("预约门诊");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("到店状态");
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue("回访状态");
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue("回访人");
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue("回访时间");
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue("回访内容");
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue("预约时间");
            cell.setCellStyle(style);
            cell = row.createCell(10);
            cell.setCellValue("初诊时间");
            cell.setCellStyle(style);
            cell = row.createCell(11);
            cell.setCellValue("建档人");
            cell.setCellStyle(style);
            cell = row.createCell(12);
            cell.setCellValue("跟进人");
            cell.setCellStyle(style);
            cell = row.createCell(13);
            cell.setCellValue("归属人");
            cell.setCellStyle(style);
            cell = row.createCell(14);
            cell.setCellValue("部门");
            cell.setCellStyle(style);
            cell = row.createCell(15);
            cell.setCellValue("建档时间");
            cell.setCellStyle(style);
            cell = row.createCell(16);
            cell.setCellValue("一级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(17);
            cell.setCellValue("二级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(18);
            cell.setCellValue("三级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(19);
            cell.setCellValue("性别");
            cell.setCellStyle(style);
            cell = row.createCell(20);
            cell.setCellValue("年龄");
            cell.setCellStyle(style);
            cell = row.createCell(21);
            cell.setCellValue("预约病种");
            cell.setCellStyle(style);
            cell = row.createCell(22);
            cell.setCellValue("访问病种");
            cell.setCellStyle(style);

            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                ReturnVisitStatistics map = list.get(i);
                row = sheet.createRow(i + 1);
                // 第四步，创建单元格，并设置值
                for (int j = 0; j <= 23; j++) {
                    row.createCell(j).setCellStyle(style);
                }
                row.getCell(0).setCellValue(map.getReservationsNum());
                row.getCell(1).setCellValue(map.getCustomerName());
                if(ShiroKit.getUser().getPhonePrivilegesexp()==1) {
                    row.getCell(2).setCellValue(map.getCustomerPhone());
                }else{
                    row.getCell(2).setCellValue("无");
                }
                row.getCell(3).setCellValue(map.getOutpatientName());
                row.getCell(4).setCellValue(map.getStatus());
                row.getCell(5).setCellValue(map.getStatuss());
                row.getCell(6).setCellValue(map.getRevisit());
                row.getCell(7).setCellValue(map.getRevisitTime());
                row.getCell(8).setCellValue(map.getRevisitContent());
                row.getCell(9).setCellValue(map.getAppointmentTime());
                row.getCell(10).setCellValue(map.getFirstVisitDate());
                row.getCell(11).setCellValue(map.getArchiving());
                row.getCell(12).setCellValue(map.getFollower());
                row.getCell(13).setCellValue(map.getBelonger());
                row.getCell(14).setCellValue(map.getDepartment());
                row.getCell(15).setCellValue(map.getFilingTime());
                row.getCell(16).setCellValue(map.getOneChannel());
                row.getCell(17).setCellValue(map.getTwoChannel());
                row.getCell(18).setCellValue(map.getThrChannel());
                row.getCell(19).setCellValue(map.getSex());
                row.getCell(20).setCellValue(map.getCustomerAge());
                row.getCell(21).setCellValue(map.getAppointmentPayService());
                row.getCell(22).setCellValue(map.getAccessDisease());
            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new java.util.Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

